package cj.web.cc.dao;

import java.util.List;
import java.util.Map;

import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.ReturnGeneratedKeys;
import net.paoding.rose.jade.annotation.SQL;

@DAO
public interface OnlineOrderDAO {

	@SQL("select name,duration,type,price,img,maxApplyCount,startDate,endDate from Game where id=:1 ")
	List<Map> getGameById(int gameId);
	
	@SQL("select id,name from Grade where deleteFlag=1 ")
    List<Map> asGradeEnum();

	@ReturnGeneratedKeys
	@SQL("INSERT INTO ComposeOnlineOrder (orderNo,userId,gameId,duration,price,"
			+ "parentName,parentPhone,name,identityCard,school,grade,source,status,deleteFlag,createAt,"
			+ "createUser,updateAt,updateUser,area,teacher,isExam,isCheck,isWin,subjectId) "
			+ "VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,1,now(),:14,now(),:14,:15,:16,0,0,0,:17) ")
	int createOrder(String orderNo, String memberId, int gameId, String duration, String price,
			String parentName, String parentPhone, String name,String identityCard, String school, 
			String grade, int source, int status, String phone, String area,
			String teacher, int subjectId);

	@SQL("SELECT id,name FROM Area WHERE deleteFlag=1 ")
	List<Map> asAreaEnum();

	@SQL("select id,price,status,payOrder,duration,name,code from ComposeOnlineOrder where deleteFlag=1 and orderNo=:1 ")
	List<Map> getOrderByNo(String outTradeNo);

	@SQL("update ComposeOnlineOrder set payOrder=:2,status=:3,payWay=:4,updateAt=NOW(),code=:5 where orderNo=:1 ")
	void updatePayStatusByNo(String out_trade_no, String payOrder, int status, int payWay,String code);

	@SQL("SELECT id,orderNo,code,userId,gameId,duration,price,parentName,parentPhone,name,identityCard,school,grade,payWay,source,status,deleteFlag,createAt,createUser,updateAt,updateUser,area,teacher,subjectId,composition FROM ComposeOnlineOrder WHERE deleteFlag=1 AND id=:1 ")
	List<Map> getOrderById(int orderId);
	
	@SQL("SELECT count(1) FROM ComposeOnlineOrder WHERE deleteFlag=1 AND identityCard=:1 AND gameId=:2 AND id not in(:3)")
	int checkOrderByIdentityCard(String identityCard,int gameId,int orderId);

	@SQL("UPDATE ComposeOnlineOrder SET name=:2,identityCard=:3,area=:4,school=:5,grade=:6,teacher=:7,parentName=:8,parentPhone=:9,updateAt=now(),updateUser=:10 WHERE id=:1 ")
	void updateOrder(int orderId,String name,String identityCard,String area,String school,String grade,String teacher,
			String parentName,String parentPhone,String updateUser);

	@SQL("SELECT id,isExam,isCheck,orderNo,code,userId,gameId,duration,price,parentName,parentPhone,name,identityCard,school,grade,payWay,source,status,deleteFlag,createAt,createUser,updateAt,updateUser,area,teacher FROM ComposeOnlineOrder WHERE deleteFlag=1 AND userId=:1 ORDER BY createAt DESC ")
	List<Map> getOrderByUserId(int userId);

	@SQL("select createUser,status from ComposeOnlineOrder where deleteFlag=1 and identityCard=:1 and gameId=:2 ")
	List<Map> getOrderByIDCard(String identityCard, int gameId);

	@SQL("SELECT id,isExam,isCheck,orderNo,code,userId,gameId,duration,price,parentName,parentPhone,name,identityCard,school,grade,payWay,source,status,deleteFlag,createAt,createUser,updateAt,updateUser,area,teacher FROM ComposeOnlineOrder WHERE deleteFlag=1 AND code=:1 AND gameId=:2 ")
	List<Map> getOrderByCode(String code,int gameId);
	
	@SQL("SELECT id,isExam,isCheck,orderNo,code,userId,gameId,duration,price,parentName,parentPhone,name,identityCard,school,grade,payWay,source,status,deleteFlag,createAt,createUser,updateAt,updateUser,area,teacher FROM ComposeOnlineOrder WHERE deleteFlag=1 AND userId=:1 AND gameId=:2 ")
	List<Map> getOrderByUserId(int userId,int gameId);
	
	@SQL("SELECT id,isExam,isCheck,orderNo,code,userId,gameId,duration,price,parentName,parentPhone,name,identityCard,school,grade,payWay,source,status,deleteFlag,createAt,createUser,updateAt,updateUser,area,teacher FROM ComposeOnlineOrder WHERE deleteFlag=1 AND userId=:1 ORDER BY createAt DESC LIMIT :2,:3 ")
	List<Map> getOrderByUserId(int userId,int start,int length);
	
	@SQL("SELECT count(1) FROM ComposeOnlineOrder WHERE deleteFlag=1 AND userId=:1 ORDER BY createAt DESC ")
	int getOrderByUserIdSize(int userId);
	
	@SQL("SELECT gameId,code,level,name,teacher,school,grade,parentName,parentPhone,payStatus,path FROM ReportCardOnline WHERE gameId=:1 AND code =:2 ")
    List<Map> queryByGameIdAndCode(int gameId,String code);
	
	//查询
	//@SQL("SELECT id,gameId,code,level,name,teacher,school,grade,parentName,parentPhone,payStatus,path,area,th,score,remark FROM ReportCardOnline WHERE gameId=:1 #if(:2!=''){ AND code LIKE '%##(:2)%' } #if(:3!=''){ AND name LIKE '%##(:3)%' } LIMIT :4,:5 ")
    //List<Map> queryReportCardByGCN(int gameId, String code, String name, int start, int length);
	@SQL("SELECT r.id,r.gameId,code,level,r.name,teacher,school,grade,parentName,parentPhone,payStatus,path,g.name as gamename FROM ReportCard as r,game as g WHERE  r.gameId = g.id and r.gameId=:1 #if(:2!=''){ AND code LIKE '%##(:2)%' } #if(:3!=''){ AND r.name LIKE '%##(:3)%' } LIMIT :4,:5 ")
    List<Map> queryReportCardByGCN(int gameId, String code, String name, int start, int length);
	
	//总数
	@SQL("SELECT count(1) FROM ReportCardOnline WHERE gameId=:1 #if(:2!=''){ AND code LIKE '%##(:2)%' } #if(:3!=''){ AND name LIKE '%##(:3)%' } ")
    int queryReportCardByGCNSzie(int gameId, String code, String name);
	
	//获取活动主体ID号
	@SQL("SELECT a.*,b.name as gradeName FROM ComposeSubject a JOIN Grade b ON a.gradeId=b.id WHERE gameId=:1 AND b.name=:2 ")
	List<Map> getComposeSubjectByGameIdAndGradeName(int gameId,String gradeName);
	
	@SQL("SELECT * FROM ComposeSubject WHERE id=:1 ")
	List<Map> getComposeSubjectById(int id);
	
	@SQL("UPDATE ComposeOnlineOrder SET composition=:2,updateAt=now(),updateUser=:3,isExam=1 WHERE id=:1 ")
	void updateOrder(int orderId,String composition,String updateUser);
	
	//----------支付
}